﻿
CREATE PROCEDURE [dbo].[resuma_CalculPontajSume] 
	@Anul smallint,
	@Luna smallint,
	@CodUnitate smallint,
	@IDPersoanaSelectata int
AS
DECLARE @MesajEroare varchar(500)

-------------Adaosuri
INSERT INTO [AngajatiSume]
     ([IDPersoana],[IDAngajatSalarizare],[Suma],[TipSuma],[TotalOreLucrate],[Anul],[Luna])      
     SELECT Ps.IDPersoana,Ans.IDAngajatSalarizare,0,'AD',0,@Anul,@Luna
     FROM
     [Persoane] Ps
     JOIN [AngajatiSalarizare] AnS ON Ps.IdPersoana = Ans.IdPersoana
     JOIN [AngajatiAdaosuri] AnP ON Ps.IdPersoana = AnP.IdPersoana AND Anp.IdAngajatSalarizare = Ans.IDAngajatSalarizare
     WHERE 
        (AnS.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Ans.PanaLaData)*100+MONTH(Ans.PanaLaData))
		AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
		AND (Ps.CodUnitate=@CodUnitate) AND AnP.Luna=@Luna AND Anp.Anul=@Anul
		AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata) AND 
		NOT EXISTS(Select * FROM [AngajatiSume] APS 
				   WHERE [IDPersoana]= Ps.IdPersoana AND IDAngajatSalarizare=Ans.IDAngajatSalarizare AND [TipSuma]='AD' 
				   AND Anp.Luna=APS.Luna AND Anp.Anul=APS.Anul)
	GROUP BY Ps.IDPersoana,Ans.IDAngajatSalarizare				   
	IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la completare sporuri'
		GOTO EROARE2				   
	END	

INSERT INTO [AngajatiSume]
     ([IDPersoana],[IDAngajatSalarizare],[Suma],[TipSuma],[TotalOreLucrate],[Anul],[Luna])      
     SELECT Ps.IDPersoana,Ans.IDAngajatSalarizare,0,'S',0,@Anul,@Luna
     FROM
     [Persoane] Ps
     JOIN [AngajatiSalarizare] AnS ON Ps.IdPersoana = Ans.IdPersoana
     JOIN [CalculSporuri] AnP ON Ps.IdPersoana = AnP.IdPersoana AND Anp.IdAngajatSalarizare = Ans.IDAngajatSalarizare
     WHERE 
        (AnS.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Ans.PanaLaData)*100+MONTH(Ans.PanaLaData))
		AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
		AND (Ps.CodUnitate=@CodUnitate) AND AnP.Luna=@Luna AND Anp.Anul=@Anul
		AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata) AND 
		NOT EXISTS(Select * FROM [AngajatiSume] APS 
				   WHERE [IDPersoana]= Ps.IdPersoana AND IDAngajatSalarizare=Ans.IDAngajatSalarizare AND [TipSuma]='S'
				   AND Anp.Luna=APS.Luna AND Anp.Anul=APS.Anul)
	GROUP BY Ps.IDPersoana,Ans.IDAngajatSalarizare				   
	IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la completare sporuri'
		GOTO EROARE2				   
	END	

---adauga pozitille pentru care nu exista pontaj
	INSERT INTO [AngajatiSume]
     ([IDPersoana],[IDAngajatSalarizare],[Suma],[TipSuma],[TotalOreLucrate],[Anul],[Luna])      
     SELECT Ps.IDPersoana,Ans.IDAngajatSalarizare,0,'P',0,@Anul,@Luna
     FROM
     [Persoane] Ps
     JOIN [AngajatiSalarizare] AnS ON Ps.IdPersoana = Ans.IdPersoana
     JOIN [AngajatiPontaj] AnP ON Ps.IdPersoana = AnP.IdPersoana AND Anp.IdAngajatSalarizare = Ans.IDAngajatSalarizare
     JOIN [TipPrezenta] Tp ON Tp.IDTipPrezenta = AnP.IDTipPrezenta
     WHERE 
        (AnS.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Ans.PanaLaData)*100+MONTH(Ans.PanaLaData))
		AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
		AND (Ps.CodUnitate=@CodUnitate) AND AnP.Luna=@Luna AND Anp.Anul=@Anul
		AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata) AND 
		NOT EXISTS(Select * FROM [AngajatiSume] APS 
				   WHERE [IDPersoana]= Ps.IdPersoana AND IDAngajatSalarizare=Ans.IDAngajatSalarizare AND [TipSuma]='P')
	GROUP BY  Ps.IDPersoana,Ans.IDAngajatSalarizare

	IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la completare pontaje'
		GOTO EROARE2				   
	END	
				 
--sterge pozitiile eliminate

DELETE FROM [AngajatiSume] WHERE [TipSuma] in ('P','S','AD') AND
	NOT EXISTS(SELECT * FROM
     [Persoane] Ps
     JOIN [AngajatiSalarizare] AnS ON Ps.IdPersoana = Ans.IdPersoana
     JOIN [AngajatiPontaj] AnP ON Ps.IdPersoana = AnP.IdPersoana AND Anp.IdAngajatSalarizare = Ans.IDAngajatSalarizare
     JOIN [TipPrezenta] Tp ON Tp.IDTipPrezenta = AnP.IDTipPrezenta
     WHERE 
        (AnS.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Ans.PanaLaData)*100+MONTH(Ans.PanaLaData))
		AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
		AND (Ps.CodUnitate=@CodUnitate) 
		AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata) AND
		[AngajatiSume].[IDPersoana]= Ps.IdPersoana AND [AngajatiSume].IDAngajatSalarizare=Ans.IDAngajatSalarizare )


	IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la stergere inexistente'
		GOTO EROARE2				   
	END	


UPDATE [AngajatiSume] SET 
   [Suma] = (SELECT Coalesce(SUM(ROUND(AnP.Valoare*Anp.SalarUnitar,0)),0)
     FROM
     [Persoane] Ps
     JOIN [AngajatiSalarizare] AnS ON Ps.IdPersoana = Ans.IdPersoana
     JOIN [AngajatiPontaj] AnP ON Ps.IdPersoana = AnP.IdPersoana AND Anp.IdAngajatSalarizare = Ans.IDAngajatSalarizare

     WHERE 
        (AnS.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Ans.PanaLaData)*100+MONTH(Ans.PanaLaData))
		AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
		AND (Ps.CodUnitate=@CodUnitate) 
		AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata) AND 
		[AngajatiSume].IDPersoana = Ps.IdPersoana AND [AngajatiSume].IDAngajatSalarizare = Ans.IDAngajatSalarizare
		AND [AngajatiSume].Luna = Anp.Luna AND [AngajatiSume].Anul=Anp.Anul),
		
    [TotalOreLucrate] =(Select  Coalesce(SUM(Case WHEN Tp.TimpBaza='O' THEN ROUND(AnP.Valoare,0) 
     WHEN Tp.TimpBaza='Z' THEN ROUND(AnP.Valoare*AnS.OreNorma,0)
     WHEN Tp.TimpBaza='S' THEN ROUND(AnP.Valoare,0) END),0) FROM
     [Persoane] Ps
     JOIN [AngajatiSalarizare] AnS ON Ps.IdPersoana = Ans.IdPersoana
     JOIN [AngajatiPontaj] AnP ON Ps.IdPersoana = AnP.IdPersoana AND Anp.IdAngajatSalarizare = Ans.IDAngajatSalarizare
     JOIN [TipPrezenta] Tp ON Tp.IDTipPrezenta = AnP.IDTipPrezenta
     WHERE 
        (AnS.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Ans.PanaLaData)*100+MONTH(Ans.PanaLaData))
		AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
		AND (Ps.CodUnitate=@CodUnitate) 
		AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata) AND 
		[AngajatiSume].IDPersoana = Ps.IdPersoana AND [AngajatiSume].IDAngajatSalarizare = Ans.IDAngajatSalarizare 
		AND [AngajatiSume].Luna = Anp.Luna AND [AngajatiSume].Anul=Anp.Anul)
	WHERE [TipSuma]='P'	AND [Luna]=@Luna AND [Anul]=@Anul

	IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la update pontaje'
		GOTO EROARE2				   
	END	


UPDATE [AngajatiSume] SET 
   [Suma] = (SELECT Coalesce(SUM(Valoare),0)
     FROM
     [Persoane] Ps
     JOIN [AngajatiSalarizare] AnS ON Ps.IdPersoana = Ans.IdPersoana
     JOIN [CalculSporuri] AnP ON Ps.IdPersoana = AnP.IdPersoana AND Anp.IdAngajatSalarizare = Ans.IDAngajatSalarizare
     WHERE 
        (AnS.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Ans.PanaLaData)*100+MONTH(Ans.PanaLaData))
		AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
		AND (Ps.CodUnitate=@CodUnitate)  AND AnP.Luna=@luna AND Anp.Anul=@anul
		AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata) AND 
	    [AngajatiSume].IDPersoana = Ps.IdPersoana AND [AngajatiSume].IDAngajatSalarizare = Ans.IDAngajatSalarizare),
    [TotalOreLucrate] =0
	WHERE [TipSuma]='S'  AND [Luna]=@Luna AND [Anul]=@Anul

	IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la update sporuri'
		GOTO EROARE2				   
	END	
----Adaosuri
UPDATE [AngajatiSume] SET 
   [Suma] = (SELECT Coalesce(SUM(suma),0)
     FROM
     [Persoane] Ps
     JOIN [AngajatiSalarizare] AnS ON Ps.IdPersoana = Ans.IdPersoana
     JOIN [AngajatiAdaosuri] AnP ON Ps.IdPersoana = AnP.IdPersoana AND Anp.IdAngajatSalarizare = Ans.IDAngajatSalarizare
     WHERE 
        (AnS.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Ans.PanaLaData)*100+MONTH(Ans.PanaLaData))
		AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
		AND (Ps.CodUnitate=@CodUnitate)  AND AnP.Luna=@luna AND Anp.Anul=@anul
		AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata) AND 
	    [AngajatiSume].IDPersoana = Ps.IdPersoana AND [AngajatiSume].IDAngajatSalarizare = Ans.IDAngajatSalarizare),
    [TotalOreLucrate] =0
	WHERE [TipSuma]='AD'  AND [Luna]=@Luna AND [Anul]=@Anul

	IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la update sporuri'
		GOTO EROARE2				   
	END	


GOTO Gata
EROARE:
	RAISERROR ('Eroare la recalcularea salariilor!',16,-1)
	GOTO Gata
EROARE1:
	RAISERROR ('!!',16,-1)	
	GOTO Gata
EROARE2:
	RAISERROR (@MesajEroare,16,5)	
	GOTO Gata
	
Gata: